﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data.SqlClient;
using System.Data;
using System.Diagnostics;
using System.Configuration;

using PRET.ReportDataDefine.DBData;

namespace PRET.PRETService.Data
{
    public class WinCCDataProcess2 : IDisposable
    {
        private System.Diagnostics.EventLog log;
        //private static SqlConnection conn = null;
        private Dictionary<string, List<string>> devicePrefixDic = new Dictionary<string, List<string>>();
        private List<string> highLowDeviceID = new List<string>();
        private readonly static string JCJName = "JCJ";
        private readonly static string JHJName = "JHJ";
        private readonly static int DaySeconds = 3 * 24 * 3600;
        private readonly static DateTime StartDay = DateTime.Parse("2015/02/01 00:00");
        // 变量枚举类型
        private enum VariableType { JCJSummaryType = 1, JCJDetailType, JHJDataType, TeamType, WaitingOrderType };
        #region WINCC变量列表
        private readonly static string[] JCVariableList = new string[] {
            "ProLine1", //生产线编号——低位
            "ProLine2", //生产线编号——高位
            "OrderNum1", //当前产品订单号——字母代号
            "OrderNum2", //当前产品订单号——高位
            "OrderNum3", //当前产品订单号——中位
            "OrderNum4", //当前产品订单号——低位
            "BalScale1", //1#秤设定比例
            "BalScale2", //2#秤设定比例
            "BalScale3", //3#秤设定比例
            "BalRCount1", //1#秤累计下料量实际值
            "BalRCount2", //2#秤累计下料量实际值
            "BalRCount3", //3#秤累计下料量实际值
            "WLJRev", //体积喂料机转速实际值
            "JTemOff1", //挤出机温度1区允差范围
            "JTemOff2", //挤出机温度2区允差范围
            "JTemOff3", //挤出机温度3区允差范围
            "JTemOff4", //挤出机温度4区允差范围
            "JTemOff5", //挤出机温度5区允差范围
            "JTemOff6", //挤出机温度6区允差范围
            "JTemOff7", //挤出机温度7区允差范围
            "JTemOff8", //挤出机温度8区允差范围
            "JTemOff9", //挤出机温度9区允差范围
            "JTemOff10", //挤出机温度10区允差范围
            "JTemSet1", //挤出机温度1区设定值
            "JTemSet2", //挤出机温度2区设定值
            "JTemSet3", //挤出机温度3区设定值
            "JTemSet4", //挤出机温度4区设定值
            "JTemSet5", //挤出机温度5区设定值
            "JTemSet6", //挤出机温度6区设定值
            "JTemSet7", //挤出机温度7区设定值
            "JTemSet8", //挤出机温度8区设定值
            "JTemSet9", //挤出机温度9区设定值
            "JTemSet10", //挤出机温度10区设定值
            "JTemReal1", //挤出机温度1区实际值
            "JTemReal2", //挤出机温度2区实际值
            "JTemReal3", //挤出机温度3区实际值
            "JTemReal4", //挤出机温度4区实际值
            "JTemReal5", //挤出机温度5区实际值
            "JTemReal6", //挤出机温度6区实际值
            "JTemReal7", //挤出机温度7区实际值
            "JTemReal8", //挤出机温度8区实际值
            "JTemReal9", //挤出机温度9区实际值
            "JTemReal10", //挤出机温度10区实际值
            "JMaxRev", //挤出机转速上限
            "JMinRev", //挤出机转速下限
            "JRealRev", //挤出机转速实际值
            "JMaxCur", //挤出机电流上限
            "JMinCur", //挤出机电流下限
            "JRealCur", //挤出机电流实际值
            "Ia", // 电流1
            "Ib", // 电流2
            "Ic", // 电流3
            "power", // 功率
            "degreeH", // 电表高位
            "degreeL", // 电表低位
            "MaxRZ", //产品熔指上限
            "MinRZ", //产品熔指下限
            "RealRz", //产品熔指实际值
            "MaxRemn", //产品残余上限
            "MinRemn", //产品残余下限
            "RealRemn", //产品残余实际值
            "JsProduce" //挤出机生产状态
        };
        private readonly static string[] JHVariableList = new string[] {
            "ProLine1", //生产线编号——低位
            "ProLine2", //生产线编号——高位
            "OrderNum1", //当前产品订单号——低位
            "OrderNum2", //当前产品订单号——中位
            "OrderNum3", //当前产品订单号——高位
            "OrderNum4", //当前产品订单号——字母代号
            "ZDTemper", //B桶锥度物料实际温度
            "ARealTem", // A桶物料实际温度
            "ABStartT", //A至B桶输送运行状态
            "BHeatST", //B桶加热运行状态
            "BThrowST", //B桶放料包装运行状态
            "UnitPC", // 单包公斤
            "UnitTC", // 放料次数
            "PJobSP", //订单开始指示
            "Ia", // 电流1
            "Ib", // 电流2
            "Ic", // 电流3
            "power", // 功率
            "degree" // 电表
        };
        private readonly static string[] WaitingVarList = new string[] {
            "ProLine1", //生产线编号——低位
            "ProLine2", //生产线编号——高位
            "WOrderNum1", //待产产品订单号——低位
            "WOrderNum2", //待产产品订单号——中位
            "WOrderNum3", //待产产品订单号——高位
            "WOrderNum4" //待产产品订单号——字母代号
        };
        private readonly static string[] TeamVariableList = new string[]{
            "ProLine1", //生产线编号——低位
            "ProLine2", //生产线编号——高位
            "TeamName1", //当前班组
            "Mixinger1", //混料员工号——低位
            "Mixinger2", //混料员工号——高位
            "Extruder1", //挤出员工号——低位
            "Extruder2", //挤出员工号——高位
            "Packer1", //包装员工号——低位
            "Packer2", //包装员工号——高位
            "Qualer1", //质量员工号——低位
            "Qualer2" //质量员工号——高位
        };
        private readonly static string[] HighLowVariableList = new string[]{
            "Ia", //电流a
            "Ib", //电流b
            "Ic", //电流c
            "Uab", //电压ab
            "Ubc", //电压bc
            "Uca", //电压ca
            "power", //负荷
            "cos", //功率因子
            "degree" // 电度
        };
        private readonly static string[] HumTemVariableList = new string[]{
            "TEM_HUMHUM", // 湿度
            "TEM_HUMTEM" // 温度
        };
        private readonly static string[] HSMLJVariableList = new string[]{
            "HS_PLCREF_feed", //开关变量
            "HS_PLCMA_SV", //主料A
            "HS_PLCMB_SV", //主料B
            "HS_PLCASA_SV", //辅料A
            "HS_PLCASB_SV", //辅料B
            "degree" // 电度数
        };
        #endregion

        #region SQL 语句
        private readonly static string CREATE_WINCCSQL_DB = "CREATE DATABASE [winccSQL] ON  PRIMARY" +
            "( NAME = N'pret', FILENAME = N'C:\\Program Files (x86)\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\DATA\\winccSQL.mdf' , SIZE = 100MB , MAXSIZE = UNLIMITED, FILEGROWTH = 30MB )" +
            "LOG ON" +
            "( NAME = N'reportsystem_log', FILENAME = N'C:\\Program Files (x86)\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\DATA\\winccSQL_log.ldf' , SIZE = 5120KB , MAXSIZE = 2048GB , FILEGROWTH = 20%)";
        private readonly static string CREATE_JC_TABLE = "CREATE TABLE [winccSQL].[dbo].[JCVariable](" +
            "[TraceID] [bigint] IDENTITY(1,1) NOT NULL," +
            "[ProLineCode] [varchar](10) NOT NULL," + // 挤出机代号
            "[Time] [datetime] NOT NULL," + // WINCC变量记录时间
            "[ProLine1] [int] NOT NULL," + //生产线编号——低位
            "[ProLine2] [int] NOT NULL," + //生产线编号——高位
            "[OrderNum1] [int] NOT NULL," + //当前产品订单号——字母代号
            "[OrderNum2] [int] NOT NULL," + //当前产品订单号——高位
            "[OrderNum3] [int] NOT NULL," + //当前产品订单号——中位
            "[OrderNum4] [int] NOT NULL," + //当前产品订单号——低位
            "[BalScale1] [int] NOT NULL," + //1#秤设定比例
            "[BalScale2] [int] NOT NULL," + //2#秤设定比例
            "[BalScale3] [int] NOT NULL," + //3#秤设定比例
            "[BalRCount1] [int] NOT NULL," + //1#秤累计下料量实际值
            "[BalRCount2] [int] NOT NULL," + //2#秤累计下料量实际值
            "[BalRCount3] [int] NOT NULL," + //3#秤累计下料量实际值
            "[WLJRev] [int] NOT NULL," + //体积喂料机转速实际值
            "[JTemOff1] [int] NOT NULL," + //挤出机温度1区允差范围
            "[JTemOff2] [int] NOT NULL," + //挤出机温度2区允差范围
            "[JTemOff3] [int] NOT NULL," + //挤出机温度3区允差范围
            "[JTemOff4] [int] NOT NULL," + //挤出机温度4区允差范围
            "[JTemOff5] [int] NOT NULL," + //挤出机温度5区允差范围
            "[JTemOff6] [int] NOT NULL," + //挤出机温度6区允差范围
            "[JTemOff7] [int] NOT NULL," + //挤出机温度7区允差范围
            "[JTemOff8] [int] NOT NULL," + //挤出机温度8区允差范围
            "[JTemOff9] [int] NOT NULL," + //挤出机温度9区允差范围
            "[JTemOff10] [int] NOT NULL," + //挤出机温度10区允差范围
            "[JTemSet1] [int] NOT NULL," + //挤出机温度1区设定值
            "[JTemSet2] [int] NOT NULL," + //挤出机温度2区设定值
            "[JTemSet3] [int] NOT NULL," + //挤出机温度3区设定值
            "[JTemSet4] [int] NOT NULL," + //挤出机温度4区设定值
            "[JTemSet5] [int] NOT NULL," + //挤出机温度5区设定值
            "[JTemSet6] [int] NOT NULL," + //挤出机温度6区设定值
            "[JTemSet7] [int] NOT NULL," + //挤出机温度7区设定值
            "[JTemSet8] [int] NOT NULL," + //挤出机温度8区设定值
            "[JTemSet9] [int] NOT NULL," + //挤出机温度9区设定值
            "[JTemSet10] [int] NOT NULL," + //挤出机温度10区设定值
            "[JTemReal1] [int] NOT NULL," + //挤出机温度1区实际值
            "[JTemReal2] [int] NOT NULL," + //挤出机温度2区实际值
            "[JTemReal3] [int] NOT NULL," + //挤出机温度3区实际值
            "[JTemReal4] [int] NOT NULL," + //挤出机温度4区实际值
            "[JTemReal5] [int] NOT NULL," + //挤出机温度5区实际值
            "[JTemReal6] [int] NOT NULL," + //挤出机温度6区实际值
            "[JTemReal7] [int] NOT NULL," + //挤出机温度7区实际值
            "[JTemReal8] [int] NOT NULL," + //挤出机温度8区实际值
            "[JTemReal9] [int] NOT NULL," + //挤出机温度9区实际值
            "[JTemReal10] [int] NOT NULL," + //挤出机温度10区实际值
            "[JMaxRev] [int] NOT NULL," + //挤出机转速上限
            "[JMinRev] [int] NOT NULL," + //挤出机转速下限
            "[JRealRev] [int] NOT NULL," + //挤出机转速实际值
            "[JMaxCur] [int] NOT NULL," + //挤出机电流上限
            "[JMinCur] [int] NOT NULL," + //挤出机电流下限
            "[JRealCur] [int] NOT NULL," + //挤出机电流实际值
            "[Ia] [int] NOT NULL," + // 电流1
            "[Ib] [int] NOT NULL," + // 电流2
            "[Ic] [int] NOT NULL," + // 电流3
            "[power] [int] NOT NULL," + // 功率
            "[degreeH] [int] NOT NULL," + // 电表高位
            "[degreeL] [int] NOT NULL," + // 电表低位
            "[MaxRZ] [int] NOT NULL," + //产品熔指上限
            "[MinRZ] [int] NOT NULL," + //产品熔指下限
            "[RealRz] [int] NOT NULL," + //产品熔指实际值
            "[MaxRemn] [int] NOT NULL," + //产品残余上限
            "[MinRemn] [int] NOT NULL," + //产品残余下限
            "[RealRemn] [int] NOT NULL," + //产品残余实际值
            "[JsProduce] [int] NOT NULL" + //挤出机生产状态
            ") ON [PRIMARY]";
        private readonly static string CREATE_JH_TABLE = "CREATE TABLE [winccSQL].[dbo].[JHVariable](" +
            "[TraceID] [bigint] IDENTITY(1,1) NOT NULL," +
            "[ProLineCode] [varchar](10) NOT NULL," + // 均化机代号
            "[Time] [datetime] NOT NULL," + // WINCC变量记录时间
            "[ProLine1] [int] NOT NULL," + //生产线编号——低位
            "[ProLine2] [int] NOT NULL," + //生产线编号——高位
            "[OrderNum1] [int] NOT NULL," + //当前产品订单号——字母代号
            "[OrderNum2] [int] NOT NULL," + //当前产品订单号——高位
            "[OrderNum3] [int] NOT NULL," + //当前产品订单号——中位
            "[OrderNum4] [int] NOT NULL," + //当前产品订单号——低位
            "[ZDTemper] [int] NOT NULL," + //B桶锥度物料实际温度
            "[ARealTem] [int] NOT NULL," + // A桶物料实际温度
            "[ABStartT] [int] NOT NULL," + //A至B桶输送运行状态
            "[BHeatST] [int] NOT NULL," + //B桶加热运行状态
            "[BThrowST] [int] NOT NULL," + //B桶放料包装运行状态
            "[UnitPC] [int] NOT NULL," + // 单包公斤
            "[UnitTC] [int] NOT NULL," + // 放料次数
            "[PJobSP] [int] NOT NULL," + //订单开始指示
            "[Ia] [int] NOT NULL," + // 电流1
            "[Ib] [int] NOT NULL," + // 电流2
            "[Ic] [int] NOT NULL," + // 电流3
            "[power] [int] NOT NULL," + // 功率
            "[degree] [int] NOT NULL" + // 电表
            ") ON [PRIMARY]";
        private readonly static string CREATE_WAITING_TABLE = "CREATE TABLE [winccSQL].[dbo].[WaitingVariable](" +
            "[TraceID] [bigint] IDENTITY(1,1) NOT NULL," +
            "[ProLineCode] [varchar](10) NOT NULL," + // 挤出机代号
            "[Time] [datetime] NOT NULL," + // WINCC变量记录时间
            "[ProLine1] [int] NOT NULL," + //生产线编号——低位
            "[ProLine2] [int] NOT NULL," + //生产线编号——高位
            "[WOrderNum1] [int] NOT NULL," + //待产产品订单号——低位
            "[WOrderNum2] [int] NOT NULL," + //待产产品订单号——中位
            "[WOrderNum3] [int] NOT NULL," + //待产产品订单号——高位
            "[WOrderNum4] [int] NOT NULL" + //待产产品订单号——字母代号
            ") ON [PRIMARY]";
        private readonly static string CREATE_TEAM_TABLE = "CREATE TABLE [winccSQL].[dbo].[TeamVariable](" +
            "[TraceID] [bigint] IDENTITY(1,1) NOT NULL," +
            "[ProLineCode] [varchar](10) NOT NULL," + // 挤出机代号
            "[Time] [datetime] NOT NULL," + // WINCC变量记录时间
            "[ProLine1] [int] NOT NULL," + //生产线编号——低位
            "[ProLine2] [int] NOT NULL," + //生产线编号——高位
            "[TeamName1] [int] NOT NULL," + // 当前班组
            "[Mixinger1] [int] NOT NULL," + //混料员工号——低位
            "[Mixinger2] [int] NOT NULL," + //混料员工号——高位
            "[Extruder1] [int] NOT NULL," + //挤出员工号——低位
            "[Extruder2] [int] NOT NULL," + //挤出员工号——高位
            "[Packer1] [int] NOT NULL," + // 包装员工号——低位
            "[Packer2] [int] NOT NULL," + // 包装员工号——高位
            "[Qualer1] [int] NOT NULL," + // 质量员工号——低位
            "[Qualer2] [int] NOT NULL" + // 质量员工号——高位
            ") ON [PRIMARY]";
        private readonly static string CREATE_HIGHLOW_TABLE = "CREATE TABLE [winccSQL].[dbo].[HighLowVariable](" +
            "[TraceID] [bigint] IDENTITY(1,1) NOT NULL," +
            "[DeviceID] [varchar](20) NOT NULL," + // 机器代号
            "[Time] [datetime] NOT NULL," + // WINCC变量记录时间
            "[Ia] [int] NOT NULL," + //电流a
            "[Ib] [int] NOT NULL," + //电流b
            "[Ic] [int] NOT NULL," + //电流c
            "[Uab] [int] NOT NULL," + //电压ab
            "[Ubc] [int] NOT NULL," + //电压bc
            "[Uca] [int] NOT NULL," + //电压ca
            "[power] [int] NOT NULL," + //负荷
            "[cos] [float] NOT NULL," + // 负荷因子
            "[degree] [int] NOT NULL" + // 电度
            ") ON [PRIMARY]";
        private readonly static string CREATE_HUMTEM_TABLE = "CREATE TABLE [winccSQL].[dbo].[HumTemVariable](" +
            "[TraceID] [bigint] IDENTITY(1,1) NOT NULL," +
            "[Time] [datetime] NOT NULL," + // WINCC变量记录时间
            "[TEM_HUMHUM] [int] NOT NULL," + //湿度
            "[TEM_HUMTEM] [int] NOT NULL" + //温度
            ") ON [PRIMARY]";
        private readonly static string CREATE_HSMLJ_TABLE = "CREATE TABLE [winccSQL].[dbo].[HSMLJVariable](" +
            "[TraceID] [bigint] IDENTITY(1,1) NOT NULL," +
            "[Time] [datetime] NOT NULL," + // WINCC变量记录时间
            "[HS_PLCREF_feed] [int] NOT NULL," + //开关变量
            "[HS_PLCMA_SV] [int] NOT NULL," + //主料A
            "[HS_PLCMB_SV] [int] NOT NULL," + //主料B
            "[HS_PLCASA_SV] [int] NOT NULL," + //辅料A
            "[HS_PLCASB_SV] [int] NOT NULL," + //辅料B
            "[degree] [int] NOT NULL" + //电镀数
            ") ON [PRIMARY]";
        private readonly static string GET_JCVARIABLE_BY_TIMERANGE = "SELECT * FROM [winccSQL].[dbo].[JCVariable] " +
            "WHERE [ProLineCode] = '{0}' AND [Time] >= '{1}' AND [Time] <= '{2}' " +
            "ORDER BY [Time]";
        private readonly static string GET_JHVARIABLE_BY_TIMERANGE = "SELECT * FROM [winccSQL].[dbo].[JHVariable] " +
            "WHERE [ProLineCode] = '{0}' AND [Time] >= '{1}' AND [Time] <= '{2}' " +
            "ORDER BY [Time]";
        private readonly static string GET_WAITINGVARIABLE_BY_PROLINECODE = "SELECT * FROM [winccSQL].[dbo].[WaitingVariable] " +
            "WHERE [ProLineCode] = '{0}'";
        private readonly static string GET_TEAMVARIABLE_BY_TIMERANGE = "SELECT * FROM [winccSQL].[dbo].[TeamVariable] " +
            "WHERE [ProLineCode] = '{0}' AND [Time] >= '{1}' AND [Time] <= '{2}' " +
            "ORDER BY [Time]";
        private readonly static string GET_HIGHLOWVARIABLE_BY_TIMERANGE = "SELECT * FROM [winccSQL].[dbo].[HighLowVariable] " +
            "WHERE [DeviceID] = '{0}' AND [Time] >= '{1}' AND [Time] <= '{2}' " +
            "ORDER BY [Time]";
        private readonly static string GET_HUMTEMVARIABLE_BY_TIMERANGE = "SELECT * FROM [winccSQL].[dbo].[HumTemVariable] " +
            "WHERE [Time] >= '{0}' AND [Time] <= '{1}' " +
            "ORDER BY [Time]";
        private readonly static string GET_HSMLJVARIABLE_BY_TIMERANGE = "SELECT * FROM [winccSQL].[dbo].[HSMLJVariable] " +
            "WHERE [Time] >= '{0}' AND [Time] <= '{1}' " +
            "ORDER BY [Time]";
        private readonly static string DELETE_OLDERDATA_FROM_JCTABLE = "DELETE FROM [winccSQL].[dbo].[JCVariable] " +
            "WHERE [Time] < '{0}'";
        private readonly static string DELETE_OLDERDATA_FROM_JHTABLE = "DELETE FROM [winccSQL].[dbo].[JHVariable] " +
            "WHERE [Time] < '{0}'";
        private readonly static string DELETE_OLDERDATA_FROM_TEAMTABLE = "DELETE FROM [winccSQL].[dbo].[TeamVariable] " +
            "WHERE [Time] < '{0}'";
        private readonly static string DELETE_OLDERDATA_FROM_WAITINGTABLE = "DELETE FROM [winccSQL].[dbo].[WaitingVariable] " +
            "WHERE [Time] < '{0}'";
        private readonly static string DELETE_OLDERDATA_FROM_HIGLOWTABLE = "DELETE FROM [winccSQL].[dbo].[HighLowVariable] " +
            "WHERE [Time] < '{0}'";
        private readonly static string DELETE_OLDERDATA_FROM_HUMTEMTABLE = "DELETE FROM [winccSQL].[dbo].[HumTemVariable] " +
            "WHERE [Time] < '{0}'";
        private readonly static string DELETE_OLDERDATA_FROM_HSMLJTABLE = "DELETE FROM [winccSQL].[dbo].[HSMLJVariable] " +
            "WHERE [Time] < '{0}'";
        #endregion

        #region 初始化
        private string serverAddress = "";
        private string dbName = "";
        private string userid = "";
        private string password = "";
        private string ConnString = "";

        public WinCCDataProcess2(string serverAddress, string dbName, string userid, string password, EventLog log)
        {
            try
            {
                //if (conn == null)
                //    conn = new SqlConnection();
                this.serverAddress = serverAddress;
                this.dbName = dbName;
                this.userid = userid;
                this.password = password;
                this.log = log;
                this.ConnString = string.Format("Data Source={0};Initial Catalog={1};User Id={2};Password={3};MultipleActiveResultSets=true;", this.serverAddress, this.dbName, this.userid, this.password);
                InitializeDeviceList();
                //if (IsConnected == false)
                //    ConnectSQL();
            }
            catch (Exception ex)
            {
                string msg = "WinCCDataProcess2.WinCCDataProcess2() Failed: " + ex.Message;
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
            }
        }
        //public bool IsConnected
        //{
        //    get
        //    {
        //        if (conn.State == System.Data.ConnectionState.Closed || conn.State == System.Data.ConnectionState.Broken)
        //            return false;
        //        else
        //            return true;
        //    }
        //}

        //public void ConnectSQL()
        //{
        //    try
        //    {
        //        conn.ConnectionString = string.Format("Data Source={0};Initial Catalog={1};User Id={2};Password={3};MultipleActiveResultSets=true;", this.serverAddress, this.dbName, this.userid, this.password);
        //        conn.Open();
        //    }
        //    catch (Exception ex)
        //    {
        //        string msg = "WinCCDataProcess2.ConnectSQL() Failed: " + ex.Message;
        //        log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
        //    }
        //}

        private static object lockObj = new object();
        //public void ReConnectSQL()
        //{
        //    lock (lockObj)
        //    {
        //        if (IsConnected == false)
        //            ConnectSQL();
        //    }
        //}
        private void InitializeDeviceList()
        {
            this.devicePrefixDic = new Dictionary<string, List<string>>();
            foreach (string key in ConfigurationManager.AppSettings.Keys)
            {
                List<string> list = new List<string>();
                int count = Int32.Parse(ConfigurationManager.AppSettings[key]);
                string var = key.Substring(0, 2);
                for (int i = 1; i <= count; i++)
                {
                    list.Add(var + i.ToString());
                }
                if (list.Count > 0)
                    this.devicePrefixDic.Add(key, list);
            }

            string strDevice = ConfigurationManager.ConnectionStrings["HighLowDevice"].ConnectionString;
            string[] devices = strDevice.Split(new char[] { ';' });
            if (devices.Length > 0)
                this.highLowDeviceID = devices.ToList();
        }

        public void Dispose()
        {
            //try
            //{
            //    if (conn != null && conn.State != System.Data.ConnectionState.Closed)
            //    {
            //        conn.Close();
            //    }
            //}
            //catch (Exception ex)
            //{
            //    string msg = "WinCCDataProcess2.Dispose() Failed: " + ex.Message;
            //    log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
            //}
        }
        #endregion

        #region 创建SQL DB和Table
        public void CreateWINCCSQLDB()
        {
            this.CreateDB();
            this.CreateTables();
        }
        private void CreateDB()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                using (SqlCommand cmd = new SqlCommand(CREATE_WINCCSQL_DB, conn))
                {
                    cmd.CommandTimeout = 0; 
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException ex)
            {
                string msg = "SQLDataProcess.CreatePERTDB() Failed: " + ex.Message;
                //log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Warning);
            }
            finally
            {
                conn.Close();
            }
        }
        private void CreateTables()
        {
            CreateJCTable();
            CreateJHTable();
            CreateWaitingTable();
            CreateTeamTable();
            CreateHighLowTable();
            CreateHumTemTable();
            CreateHSMLJTable();
        }
        private void CreateJCTable()
        {
            SqlCommand cmd = null;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                using (cmd = new SqlCommand(CREATE_JC_TABLE, conn))
                {
                    cmd.CommandTimeout = 0;
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException ex)
            {
                //if (cmd != null)
                    //cmd.Dispose();
                //string msg = "SQLDataProcess.CreatePERTDB() Failed: " + ex.Message;
                //log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Warning);
            }
            finally
            {
                conn.Close();
            }
        }
        private void CreateJHTable()
        {
            SqlCommand cmd = null;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            try
            {
                using (cmd = new SqlCommand(CREATE_JH_TABLE, conn))
                {
                    cmd.CommandTimeout = 0;
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException ex)
            {
                //if (cmd != null)
                    //cmd.Dispose();
                //string msg = "SQLDataProcess.CreatePERTDB() Failed: " + ex.Message;
                //log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Warning);
            }
            finally
            {
                conn.Close();
            }
        }
        private void CreateWaitingTable()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            SqlCommand cmd = null;
            try
            {
                using (cmd = new SqlCommand(CREATE_WAITING_TABLE, conn))
                {
                    cmd.CommandTimeout = 0;
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException ex)
            {
                //if (cmd != null)
                    //cmd.Dispose();
                //string msg = "SQLDataProcess.CreatePERTDB() Failed: " + ex.Message;
                //log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Warning);
            }
            finally
            {
                conn.Close();
            }
        }
        private void CreateTeamTable()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            SqlCommand cmd = null;
            try
            {
                using (cmd = new SqlCommand(CREATE_TEAM_TABLE, conn))
                {
                    cmd.CommandTimeout = 0;
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException ex)
            {
                //if (cmd != null)
                    //cmd.Dispose();
                //string msg = "SQLDataProcess.CreatePERTDB() Failed: " + ex.Message;
                //log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Warning);
            }
            finally
            {
                conn.Close();
            }
        }
        private void CreateHighLowTable()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            SqlCommand cmd = null;
            try
            {
                using (cmd = new SqlCommand(CREATE_HIGHLOW_TABLE, conn))
                {
                    cmd.CommandTimeout = 0;
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException ex)
            {
                //if (cmd != null)
                    //cmd.Dispose();
                //string msg = "SQLDataProcess.CreateHighLowTable() Failed: " + ex.Message;
                //log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Warning);
            }
            finally
            {
                conn.Close();
            }
        }
        private void CreateHumTemTable()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            SqlCommand cmd = null;
            try
            {
                using (cmd = new SqlCommand(CREATE_HUMTEM_TABLE, conn))
                {
                    cmd.CommandTimeout = 0;
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException ex)
            {
                //if (cmd != null)
                    //cmd.Dispose();
                //string msg = "SQLDataProcess.CreateHumTemTable() Failed: " + ex.Message;
                //log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Warning);
            }
            finally
            {
                conn.Close();
            }
        }
        private void CreateHSMLJTable()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            SqlCommand cmd = null;
            try
            {
                using (cmd = new SqlCommand(CREATE_HSMLJ_TABLE, conn))
                {
                    cmd.CommandTimeout = 0;
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException ex)
            {
                //if (cmd != null)
                    //cmd.Dispose();
                //string msg = "SQLDataProcess.CreateHSMLJTable() Failed: " + ex.Message;
                //log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Warning);
            }
            finally
            {
                conn.Close();
            }
        }        
        #endregion

        #region 挤出机
        public List<JCJDataInfo> GetJCJDataFromDB(Dictionary<string, DateTime> startTime)
        {
            try
            {
                // 如果没有挤出机前缀列表，则返回null
                if (!this.devicePrefixDic.ContainsKey(JCJName))
                    return null;

                List<JCJDataInfo> retList = new List<JCJDataInfo>();
                //log.WriteEntry("开始收集挤出机数据:");
                foreach (string prefix in this.devicePrefixDic[JCJName])
                {
                    DateTime latest = StartDay;
                    if (startTime != null && startTime.Count > 0 && startTime.ContainsKey(prefix))
                        latest = startTime[prefix];

                    string msg = string.Format("从{0}开始收集挤出机summary{1}数据。", latest, prefix);
                    //log.WriteEntry(msg);
                    // 如果当前WINCC含有的数据量超过3天，我们通过多次遍历，每次遍历间隔为3天的数据来记录挤出机的数值
                    DateTime nowTime = DateTime.Now;
                    DateTime tmpTime = latest;
                    DateTime stopTime = GetStopTime(tmpTime, nowTime);
                    do
                    {
                        msg = string.Format("开始收集挤出机summary{2}数据，时间{0} - {1}", latest, stopTime, prefix);
                        //log.WriteEntry(msg);
                        JCJDataInfo info = GetJCJData(prefix, latest, stopTime);
                        if (info != null)
                        {
                            //msg = string.Format("挤出机Summary{0}取到数据数{1}", prefix, list.Count);
                            //log.WriteEntry(msg);
                            retList.Add(info);
                        }
                        // 将上次记录的时间加1秒
                        latest = stopTime.AddSeconds(1);
                        tmpTime = latest;
                        stopTime = GetStopTime(tmpTime, nowTime);
                    } while (latest < stopTime); 
                }
                return retList;
            }
            catch (Exception ex)
            {
                string msg = string.Format("WinCCDataProcess.GetJCJSummaryData() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
                return null;
            }
        }
        /// <summary>
        /// 获取指定挤出机生产线的summary生产数据
        /// </summary>
        /// <param name="prefix"></param>
        /// <param name="startTime"></param>
        /// <returns></returns>
        private JCJDataInfo GetJCJData(string prefix, DateTime startTime, DateTime stopTime)
        {
            try
            {
                JCJDataInfo obj = new JCJDataInfo();
                List<JCJSummaryInfo> summaryList = new List<JCJSummaryInfo>();
                string query = string.Format(GET_JCVARIABLE_BY_TIMERANGE, prefix, startTime, stopTime);
                Dictionary<string, List<string>> varDic = GetWINCCVariableList(query, JCVariableList);
                if (varDic == null || varDic.Count == 0)
                {
                    //string msg = string.Format("挤出机Summary{0}的记录为空。", prefix);
                    //log.WriteEntry(msg);
                    return null;
                }

                // 记录挤出机Summary数据
                Dictionary<string, List<string>> summaryDic = new Dictionary<string, List<string>>();
                // 如果第一个JsProduce的值为0，那么这个标志位有可能记录的是上次刚刚完结的时间
                // 所以我们在这里单独记录这个数据到一个新的JCJData 对象中
                if (varDic["JsProduce"][0] == "0")
                {
                    Dictionary<string, string> tmpDic = new Dictionary<string, string>();
                    foreach (KeyValuePair<string, List<string>> item in varDic)
                    {
                        tmpDic.Add(item.Key, item.Value[0]);
                    }
                    JCJSummaryInfo objData = JCJSummaryInfo.ConvertDictionary2SummaryWithEndTime(tmpDic, prefix);
                    summaryList.Add(objData);
                }
                else
                {
                    foreach (KeyValuePair<string, List<string>> item in varDic)
                    {
                        summaryDic.Add(item.Key, new List<string>());
                        summaryDic[item.Key].Add(item.Value[0]);
                    }
                }

                int count = varDic["JsProduce"].Count;
                for (int i = 1; i < count; i++)
                {                    
                    // 判断一个订单是否结束
                    if (varDic["JsProduce"][i] == "1")
                    {
                        foreach (KeyValuePair<string, List<string>> item in varDic)
                        {
                            if (!summaryDic.ContainsKey(item.Key))
                                summaryDic.Add(item.Key, new List<string>());
                            summaryDic[item.Key].Add(item.Value[i]);
                        }
                    }
                    else
                    {
                        // 如果前一个标志是1，当前的标志是0，说明一个包装结束, 记录包装信息和更新记录时间
                        if (varDic["JsProduce"][i - 1] == "1")
                        {
                            JCJSummaryInfo objData = JCJSummaryInfo.ConvertDictionary2Summary(summaryDic, prefix);
                            objData.EndTime = DateTime.Parse(varDic["Time"][i - 1]);
                            summaryList.Add(objData);
                            //string msg = string.Format("挤出机{0}取到生产线{1}和开始时间{2}，结束时间{3}", prefix, objData.ProductionLine, objData.StartTime, objData.EndTime);
                            //log.WriteEntry(msg);
                            summaryDic.Clear();
                        }
                    }
                }
                // 有可能当前的时间间隔里的运行标志“JsProduce”都是1， 那就需要把summaryDic里的内容存入到对象中
                if (summaryDic.Count > 1)
                {
                    JCJSummaryInfo objData = JCJSummaryInfo.ConvertDictionary2Summary(summaryDic, prefix);
                    summaryList.Add(objData);
                }

                // 保存detail数据到List<JCJDetailInfo>
                List<JCJDetailInfo> detailList = JCJDetailInfo.ConvertDictionary2JCJDetails(varDic, prefix);
                obj.JCJSummaryInfo = summaryList;
                obj.JCJDetailInfo = detailList;
                return obj;
            }
            catch (Exception ex)
            {
                string msg = string.Format("WinCCDataProcess2.GetJCJData() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
                return null;
            }
        }
        #endregion

        #region 均化机
        public JHJDataInfo GetJHJDataFromDB(Dictionary<string, DateTime> startTime)
        {
            JHJDataInfo retData = new JHJDataInfo();
            // 如果没有均化机前缀列表，则返回null
            if (!this.devicePrefixDic.ContainsKey(JHJName))
                return null;
            foreach (string prefix in this.devicePrefixDic[JHJName])
            {
                DateTime latest = StartDay;
                if (startTime != null && startTime.Count > 0 && startTime.ContainsKey(prefix))
                    latest = startTime[prefix];

                // 如果当前WINCC含有的数据量超过3天，我们通过多次遍历，每次遍历间隔为3天的数据来记录均化机的数值
                DateTime nowTime = DateTime.Now;
                DateTime tmpTime = latest;
                DateTime stopTime = GetStopTime(tmpTime, nowTime);
                //string msg = string.Format("开始收集均化机{0}数据", prefix);
                //log.WriteEntry(msg);
                do
                {
                    //string msg = string.Format("开始收集均化机{2}数据，时间{0} - {1}", latest, stopTime, prefix);
                    //log.WriteEntry(msg);
                    JHJDataInfo dataInfo = GetJHJData(prefix, latest, stopTime);
                    if (dataInfo != null)
                    {
                        //msg = string.Format("挤出机{0}取到数据数{1}", prefix, list.Count);
                        //log.WriteEntry(msg);
                        if (dataInfo.JHJInfoList != null && dataInfo.JHJInfoList.Count > 0)
                        {
                            if (retData.JHJInfoList == null)
                                retData.JHJInfoList = new List<JHJData>();
                            retData.JHJInfoList.AddRange(dataInfo.JHJInfoList);
                        }
                        if (dataInfo.JHJDetailList != null && dataInfo.JHJDetailList.Count > 0)
                        {
                            if (retData.JHJDetailList == null)
                                retData.JHJDetailList = new List<JHJDetailInfo>();
                            retData.JHJDetailList.AddRange(dataInfo.JHJDetailList);
                        }
                    }
                    // 将上次记录的时间加1秒
                    latest = stopTime.AddSeconds(1);
                    tmpTime = latest;
                    stopTime = GetStopTime(tmpTime, nowTime);
                } while (latest < stopTime);
            }
            return retData;
        }
        private JHJDataInfo GetJHJData(string prefix, DateTime startTime, DateTime stopTime)
        {
            try
            {
                string query = string.Format(GET_JHVARIABLE_BY_TIMERANGE, prefix, startTime, stopTime);
                Dictionary<string, List<string>> varDic = GetWINCCVariableList(query, JHVariableList);
                if (varDic == null || varDic.Count == 0)
                {
                    //string msg = string.Format("均化机{0}的记录为空。", prefix);
                    //log.WriteEntry(msg);
                    return null;
                }

                // 记录均化机数据
                JHJDataInfo retData = new JHJDataInfo();
                List<JHJData> JHJList = new List<JHJData>();
                Dictionary<string, List<string>> newDic = new Dictionary<string, List<string>>();
                if (varDic["PJobSP"][0] == "1")
                {
                    foreach (KeyValuePair<string, List<string>> item in varDic)
                    {
                        newDic.Add(item.Key, new List<string>());
                        newDic[item.Key].Add(item.Value[0]);
                    }
                }
                // 如果第一个PJobSP的值为0，那么这个标志位有可能记录的是上次刚刚完结的包装时间
                // 所以我们在这里单独记录这个数据到一个新的JHJData 对象中
                else
                {
                    Dictionary<string, string> tmpDic = new Dictionary<string, string>();
                    foreach (KeyValuePair<string, List<string>> item in varDic)
                    {
                        tmpDic.Add(item.Key, item.Value[0]);
                    }
                    JHJData objData = JHJData.ConvertDictionary2JHJData(tmpDic, prefix);
                    JHJList.Add(objData);
                    //string msg = string.Format("均化机{0}取到生产线{1}和开始时间{2}", prefix, objData.ProductionLine, objData.StartTime);
                    //log.WriteEntry(msg);
                }

                int count = varDic["PJobSP"].Count;
                for (int i = 1; i < count; i++)
                {
                    if (varDic["PJobSP"][i] == "1")
                    {
                        foreach (KeyValuePair<string, List<string>> item in varDic)
                        {
                            if (!newDic.ContainsKey(item.Key))
                                newDic.Add(item.Key, new List<string>());
                            newDic[item.Key].Add(item.Value[i]);
                        }
                    }
                    else
                    {
                        // 如果前一个标志是1，当前的标志是0，说明一个包装结束, 记录包装信息和更新记录时间
                        if (varDic["PJobSP"][i - 1] == "1")
                        {
                            JHJData objData = JHJData.ConvertDictionary2JHJData(newDic, prefix);
                            objData.EndTime = DateTime.Parse(varDic["Time"][i - 1]);
                            //string msg = string.Format("均化机{0}取到生产线{1}和开始时间{2}，结束时间{3}", prefix, objData.ProductionLine, objData.StartTime, objData.EndTime);
                            //log.WriteEntry(msg);
                            JHJList.Add(objData);
                            newDic.Clear();
                        }
                    }
                }

                // 有可能当前的时间间隔里的运行标志“PJobSP”都是1， 那就需要把newDic里的内容存入到对象中
                if (newDic.Count > 1)
                {
                    JHJData objData = JHJData.ConvertDictionary2JHJData(newDic, prefix);
                    //string msg = string.Format("均化机{0}取到生产线{1}与开始时间{2}", prefix, objData.ProductionLine, objData.StartTime);
                    //log.WriteEntry(msg);
                    JHJList.Add(objData);
                }
                retData.JHJInfoList = JHJList;
                List<JHJDetailInfo> detailList = JHJDetailInfo.ConvertDictionary2JHJDetails(varDic, prefix);
                retData.JHJDetailList = detailList;
                return retData;
            }
            catch (Exception ex)
            {
                string msg = string.Format("WinCCDataProcess.GetJHJData() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
                return null;
            }
        }
        #endregion

        #region 待产信息
        public List<WaitingOrder> GetWaitingOrderListFromDB()
        {
            List<WaitingOrder> retList = new List<WaitingOrder>();
            if (!this.devicePrefixDic.ContainsKey(JCJName))
                return retList;

            foreach (string prefix in this.devicePrefixDic[JCJName])
            {
                WaitingOrder order = GetWaitingOrder(prefix);
                if (order != null)
                    retList.Add(order);
            }
            return retList;
        }
        private WaitingOrder GetWaitingOrder(string prefix)
        {
            try
            {
                string query = string.Format(GET_WAITINGVARIABLE_BY_PROLINECODE, prefix);
                Dictionary<string, List<string>> varDic = GetWINCCVariableList(query, WaitingVarList);
                if (varDic == null || varDic.Count == 0)
                    return null;

                WaitingOrder order = WaitingOrder.ConvertDictionary2WaitingOrder(varDic, prefix);
                return order;
            }
            catch (Exception ex)
            {
                string msg = string.Format("WinCCDataProcess.GetWaitingOrder() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
                return null;
            }
        }
        #endregion

        #region 班组
        public List<TeamWorkTime> GetTeamWorkFromDB(Dictionary<string, DateTime> startTime)
        {
            List<TeamWorkTime> retList = new List<TeamWorkTime>();
            // 如果没有挤出机前缀列表，则返回null
            if (!this.devicePrefixDic.ContainsKey(JCJName))
                return retList;
            // 从每个挤出机生产线上拿团队名单
            foreach (string prefix in this.devicePrefixDic[JCJName])
            {
                DateTime latest = StartDay;
                if (startTime != null && startTime.Count > 0 && startTime.ContainsKey(prefix))
                    latest = startTime[prefix];

                // 如果当前WINCC含有的数据量超过3天，我们通过多次遍历，每次遍历间隔为3天的数据来记录均化机的数值
                DateTime nowTime = DateTime.Now;
                DateTime tmpTime = latest;
                DateTime stopTime = GetStopTime(tmpTime, nowTime);
                do
                {
                    List<TeamWorkTime> list = GetTeamWorkTimeData(prefix, latest, stopTime);
                    if (list != null && list.Count > 0)
                    {
                        retList.AddRange(list);
                    }
                    // 将上次记录的时间加1秒
                    latest = stopTime.AddSeconds(1);
                    tmpTime = latest;
                    stopTime = GetStopTime(tmpTime, nowTime);
                } while (latest < stopTime);
            }
            return retList;
        }
        private List<TeamWorkTime> GetTeamWorkTimeData(string prefix, DateTime startTime, DateTime stopTime)
        {
            try
            {
                string query = string.Format(GET_TEAMVARIABLE_BY_TIMERANGE, prefix, startTime, stopTime);
                Dictionary<string, List<string>> varDic = GetWINCCVariableList(query, TeamVariableList);
                if (varDic == null || varDic.Count == 0)
                {
                    //log.WriteEntry(string.Format("班组{0}的记录为空。", prefix));
                    return null;
                }

                // 记录工作人员信息
                List<TeamWorkTime> retList = TeamWorkTime.Convert2TeamWorkTime(varDic, prefix);
                return retList;
            }
            catch (Exception ex)
            {
                string msg = string.Format("WinCCDataProcess.GetTeamWorkTimeData() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
                return null;
            }
        }
        #endregion

        #region 高低柜
        public List<HighLowInfo> GetHighLowFromDB(Dictionary<string, DateTime> deviceTime)
        {
            List<HighLowInfo> retList = new List<HighLowInfo>();
            // 每个设备的ID号
            foreach (string deviceID in this.highLowDeviceID)
            {
                DateTime latest = StartDay;
                if (deviceTime != null && deviceTime.Count > 0 && deviceTime.ContainsKey(deviceID))
                    latest = deviceTime[deviceID];

                // 如果当前WINCC含有的数据量超过3天，我们通过多次遍历，每次遍历间隔为3天的数据来记录均化机的数值
                DateTime nowTime = DateTime.Now;
                DateTime tmpTime = latest;
                DateTime stopTime = GetStopTime(tmpTime, nowTime);
                do
                {
                    List<HighLowInfo> list = GetHighLowData(deviceID, latest, stopTime);
                    if (list != null && list.Count > 0)
                    {
                        retList.AddRange(list);
                    }
                    // 将上次记录的时间加1秒
                    latest = stopTime.AddSeconds(1);
                    tmpTime = latest;
                    stopTime = GetStopTime(tmpTime, nowTime);
                } while (latest < stopTime);
            }
            return retList;
        }
        private List<HighLowInfo> GetHighLowData(string deviceID, DateTime startTime, DateTime stopTime)
        {
            try
            {
                string query = string.Format(GET_HIGHLOWVARIABLE_BY_TIMERANGE, deviceID, startTime, stopTime);
                Dictionary<string, List<string>> varDic = GetWINCCVariableList(query, HighLowVariableList);
                if (varDic == null || varDic.Count == 0)
                {
                    //log.WriteEntry(string.Format("班组{0}的记录为空。", prefix));
                    return null;
                }
                List<HighLowInfo> retList = HighLowInfo.Convert2HighLowInfo(varDic, deviceID);
                return retList;
            }
            catch (Exception ex)
            {
                string msg = string.Format("WinCCDataProcess.GetHighLowData() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
                return null;
            }
        }

        public List<HighLowVoltage> GetHighLowVoltageFromDB(Dictionary<string, DateTime> deviceTime)
        {
            List<HighLowVoltage> retList = new List<HighLowVoltage>();
            // 每个设备的ID号
            foreach (string deviceID in this.highLowDeviceID)
            {
                DateTime latest = StartDay;
                if (deviceTime != null && deviceTime.Count > 0 && deviceTime.ContainsKey(deviceID))
                    latest = deviceTime[deviceID];

                // 如果当前WINCC含有的数据量超过3天，我们通过多次遍历，每次遍历间隔为3天的数据来记录均化机的数值
                DateTime nowTime = DateTime.Now;
                DateTime tmpTime = latest;
                DateTime stopTime = GetStopTime(tmpTime, nowTime);
                do
                {
                    List<HighLowVoltage> list = GetHighLowVoltageData(deviceID, latest, stopTime);
                    if (list != null && list.Count > 0)
                    {
                        retList.AddRange(list);
                    }
                    // 将上次记录的时间加1秒
                    latest = stopTime.AddSeconds(1);
                    tmpTime = latest;
                    stopTime = GetStopTime(tmpTime, nowTime);
                } while (latest < stopTime);
            }
            return retList;
        }
        private List<HighLowVoltage> GetHighLowVoltageData(string deviceID, DateTime startTime, DateTime stopTime)
        {
            try
            {
                string query = string.Format(GET_HIGHLOWVARIABLE_BY_TIMERANGE, deviceID, startTime, stopTime);
                Dictionary<string, List<string>> varDic = GetWINCCVariableList(query, HighLowVariableList);
                if (varDic == null || varDic.Count == 0)
                {
                    //log.WriteEntry(string.Format("班组{0}的记录为空。", prefix));
                    return null;
                }
                List<HighLowVoltage> retList = HighLowVoltage.Convert2HighLowVoltage(varDic, deviceID);
                return retList;
            }
            catch (Exception ex)
            {
                string msg = string.Format("WinCCDataProcess.GetHighLowVoltageData() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
                return null;
            }
        }
        #endregion

        #region 设备湿度温度
        public List<HumidityTemperature> GetHumTemFromDB(DateTime latestTime)
        {
            List<HumidityTemperature> retList = new List<HumidityTemperature>();
            // 如果当前WINCC含有的数据量超过3天，我们通过多次遍历，每次遍历间隔为3天的数据来记录均化机的数值
            DateTime nowTime = DateTime.Now;
            DateTime tmpTime = latestTime;
            DateTime stopTime = GetStopTime(tmpTime, nowTime);
            do
            {
                List<HumidityTemperature> list = GetHumTemData(latestTime, stopTime);
                if (list != null && list.Count > 0)
                {
                    retList.AddRange(list);
                }
                // 将上次记录的时间加1秒
                latestTime = stopTime.AddSeconds(1);
                tmpTime = latestTime;
                stopTime = GetStopTime(tmpTime, nowTime);
            } while (latestTime < stopTime);
            return retList;
        }
        private List<HumidityTemperature> GetHumTemData(DateTime startTime, DateTime stopTime)
        {
            try
            {
                string query = string.Format(GET_HUMTEMVARIABLE_BY_TIMERANGE, startTime, stopTime);
                Dictionary<string, List<string>> varDic = GetWINCCVariableList(query, HumTemVariableList);
                if (varDic == null || varDic.Count == 0)
                {
                    //log.WriteEntry(string.Format("班组{0}的记录为空。", prefix));
                    return null;
                }

                // 记录工作人员信息
                List<HumidityTemperature> retList = HumidityTemperature.Convert2HumTemInfo(varDic);
                return retList;
            }
            catch (Exception ex)
            {
                string msg = string.Format("WinCCDataProcess.GetHumTemData() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
                return null;
            }
        }
        #endregion

        #region 滑石母粒机
        public List<HSMLJInfo> GetHSMLJFromDB(DateTime latestTime)
        {
            List<HSMLJInfo> retList = new List<HSMLJInfo>();
            // 如果当前WINCC含有的数据量超过3天，我们通过多次遍历，每次遍历间隔为3天的数据来记录均化机的数值
            DateTime nowTime = DateTime.Now;
            DateTime tmpTime = latestTime;
            DateTime stopTime = GetStopTime(tmpTime, nowTime);
            do
            {
                List<HSMLJInfo> list = GetHSMLJData(latestTime, stopTime);
                if (list != null && list.Count > 0)
                {
                    retList.AddRange(list);
                }
                // 将上次记录的时间加1秒
                latestTime = stopTime.AddSeconds(1);
                tmpTime = latestTime;
                stopTime = GetStopTime(tmpTime, nowTime);
            } while (latestTime < stopTime);
            return retList;
        }
        private List<HSMLJInfo> GetHSMLJData(DateTime startTime, DateTime stopTime)
        {
            try
            {
                List<HSMLJInfo> summaryList = new List<HSMLJInfo>();
                string query = string.Format(GET_HSMLJVARIABLE_BY_TIMERANGE, startTime, stopTime);
                Dictionary<string, List<string>> varDic = GetWINCCVariableList(query, HSMLJVariableList);
                if (varDic == null || varDic.Count == 0)
                {
                    //string msg = string.Format("滑石母粒机的记录为空。", prefix);
                    //log.WriteEntry(msg);
                    return null;
                }

                Dictionary<string, List<string>> summaryDic = new Dictionary<string, List<string>>();
                // 如果第一个HS_PLCREF_feed的值为0，那么这个标志位有可能记录的是上次刚刚完结的时间
                // 所以我们在这里单独记录这个数据到一个新的HSMLJInfo对象中
                if (varDic["HS_PLCREF_feed"][0] == "0")
                {
                    Dictionary<string, string> tmpDic = new Dictionary<string, string>();
                    foreach (KeyValuePair<string, List<string>> item in varDic)
                    {
                        tmpDic.Add(item.Key, item.Value[0]);
                    }
                    HSMLJInfo objData = HSMLJInfo.ConvertDictionary2HSMLJWithEndTime(tmpDic);
                    summaryList.Add(objData);
                }
                else
                {
                    foreach (KeyValuePair<string, List<string>> item in varDic)
                    {
                        summaryDic.Add(item.Key, new List<string>());
                        summaryDic[item.Key].Add(item.Value[0]);
                    }
                }

                int count = varDic["HS_PLCREF_feed"].Count;
                for (int i = 1; i < count; i++)
                {
                    // 判断一个订单是否结束
                    if (varDic["HS_PLCREF_feed"][i] == "1")
                    {
                        foreach (KeyValuePair<string, List<string>> item in varDic)
                        {
                            if (!summaryDic.ContainsKey(item.Key))
                                summaryDic.Add(item.Key, new List<string>());
                            summaryDic[item.Key].Add(item.Value[i]);
                        }
                    }
                    else
                    {
                        // 如果前一个标志是1，当前的标志是0，说明上一个刚结束, 记录滑石母粒机数据
                        if (varDic["HS_PLCREF_feed"][i - 1] == "1")
                        {
                            HSMLJInfo objData = HSMLJInfo.ConvertDictionary2HSMLJWithEndTime(summaryDic);
                            objData.EndTime = DateTime.Parse(varDic["Time"][i - 1]);
                            summaryList.Add(objData);
                            summaryDic.Clear();
                        }
                    }
                }
                // 有可能当前的时间间隔里的运行标志“HS_PLCREF_feed”都是1， 那就需要把summaryDic里的内容存入到对象中
                if (summaryDic.Count > 1)
                {
                    HSMLJInfo objData = HSMLJInfo.ConvertDictionary2HSMLJ(summaryDic);
                    summaryList.Add(objData);
                }

                return summaryList;
            }
            catch (Exception ex)
            {
                string msg = string.Format("WinCCDataProcess2.GetHSMLJData() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
                return null;
            }
        }
        #endregion

        #region 删除3天以前的数据
        public void DeleteOlderData()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = this.ConnString;
            conn.Open();
            SqlCommand command = null;
            try
            {
                string timePoint = DateTime.Now.AddDays(-3).ToString();
                string delete = string.Format(DELETE_OLDERDATA_FROM_JCTABLE, timePoint);
                using (command = new SqlCommand(delete, conn))
                {
                    command.CommandTimeout = 0;
                    command.ExecuteNonQuery();
                }
                delete = string.Format(DELETE_OLDERDATA_FROM_JHTABLE, timePoint);
                using (command = new SqlCommand(delete, conn))
                {
                    command.CommandTimeout = 0;
                    command.ExecuteNonQuery();
                }
                delete = string.Format(DELETE_OLDERDATA_FROM_TEAMTABLE, timePoint);
                using (command = new SqlCommand(delete, conn))
                {
                    command.CommandTimeout = 0;
                    command.ExecuteNonQuery();
                }
                delete = string.Format(DELETE_OLDERDATA_FROM_WAITINGTABLE, timePoint);
                using (command = new SqlCommand(delete, conn))
                {
                    command.CommandTimeout = 0;
                    command.ExecuteNonQuery();
                }
                delete = string.Format(DELETE_OLDERDATA_FROM_HIGLOWTABLE, timePoint);
                using (command = new SqlCommand(delete, conn))
                {
                    command.CommandTimeout = 0;
                    command.ExecuteNonQuery();
                }
                delete = string.Format(DELETE_OLDERDATA_FROM_HUMTEMTABLE, timePoint);
                using (command = new SqlCommand(delete, conn))
                {
                    command.CommandTimeout = 0;
                    command.ExecuteNonQuery();
                }
                delete = string.Format(DELETE_OLDERDATA_FROM_HSMLJTABLE, timePoint);
                using (command = new SqlCommand(delete, conn))
                {
                    command.CommandTimeout = 0;
                    command.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                if (command != null)
                    command.Dispose();
                string msg = string.Format("WinCCDataProcess.DeleteOlderData() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);
            }
            finally
            {
                conn.Close();
            }
        }
        #endregion


        private Dictionary<string, List<string>> GetWINCCVariableList(string query, string[] VariableList)
        {
            SqlCommand command = null;
            SqlDataReader reader = null;
            try
            {
                Dictionary<string, List<string>> varDic = new Dictionary<string, List<string>>();
                List<string> valueList = new List<string>();    // 记录变量值列表
                List<DateTime> timeList = new List<DateTime>(); // 记录时间列表
                string varName = VariableList[0];    // 变量名, 也是变量列表的关键字
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = this.ConnString;
                conn.Open();
                using (command = new SqlCommand(query, conn))
                {
                    command.CommandTimeout = 0;
                    reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader.Read())
                    {
                        DateTime time = DateTime.Parse(reader["Time"].ToString());
                        if (varDic.Keys.Contains("Time"))
                            varDic["Time"].Add(time.ToString());
                        else
                            varDic.Add("Time", new List<string> { time.ToString() });
                        foreach (string var in VariableList)
                        {
                            if (varDic.Keys.Contains(var))
                                varDic[var].Add(reader[var].ToString());
                            else
                                varDic.Add(var, new List<string> { reader[var].ToString() });
                        }
                    }
                    reader.Close();
                }
                return varDic;
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                if (command != null)
                    command.Dispose();
                string msg = string.Format("WinCCDataProcess2.GetWINCCVariableList() Failed: {0}.\r\nDetail: {1}", ex.Message, ex.StackTrace);
                log.WriteEntry(msg, System.Diagnostics.EventLogEntryType.Error);

                //ReConnectSQL();
                return null;
            }
        }
        private DateTime GetStopTime(DateTime startTime, DateTime nowTime)
        {
            TimeSpan span = (TimeSpan)(nowTime - startTime);
            double daySec = span.TotalSeconds;
            if (daySec > DaySeconds)
                return startTime.AddSeconds(DaySeconds);
            else
                return startTime.AddSeconds(daySec);
        }
    }
}
